<html>

<head>
<title>SQL Relay - Getting Started With PostgreSQL</title>
<link rel="stylesheet" href="../css/styles.css">
</head>

<body>

<span class="heading1">Getting Started With PostgreSQL</span><br>

<ul>
<li><a href="#installation">Installation</a></li>
<ul>
<li><a href="#rpm">RPM-based Linux</a></li>
<li><a href="#debian">Debian Linux</a></li>
<li><a href="#slackware">Slackware Linux</a></li>
<li><a href="#freebsd">FreeBSD</a></li>
<li><a href="#openbsd">OpenBSD</a></li>
<li><a href="#netbsd">NetBSD</a></li>
<li><a href="#openserver">SCO OpenServer</a></li>
<li><a href="#solaris">Solaris</a></li>
<li><a href="#source">Compiling From Source</a></li>
<li><a href="#startingatboot">Starting the Database at Boot Time</a></li>
<li><a href="#initial">Initial Configuration</a></li>
</ul>
<li><a href="#creating">Creating a Database</a></li>
<li><a href="#accessing">Accessing a Database</a></li>
<li><a href="#sqlrelay">Accessing a Database With SQL Relay</a></li>
<li><a href="#quirks">PostgreSQL Quirks</a></li>
</ul>

<a name="installation"></a>
<span class="heading1">Installation</span><br>

<p>I've sucessfully installed PostgreSQL on Linux, FreeBSD, NetBSD, OpenBSD,
Solaris and SCO OpenServer.</p>

<p>FreeBSD, OpenBSD, NetBSD and most Linux distributions come with PostgreSQL 
and give you the option of installing it when you install the rest of the 
system.  If you want or need to install it afterward, follow the instructions 
below.</p>

<a name="rpm"></a>
<span class="heading2">RPM-based Linux</span><br>

<p>To install PostgreSQL on an RPM-based Linux distribution like RedHat, 
Mandrake or TurboLinux, acquire the postgresql, postgresql-server, 
postgresql-devel and postgresql-libs RPMS from 
<a href="http://rpmfind.net">RPMFind</a> or possibly from the CD's that came 
with your distribution and install them using <i>rpm -i</i>.</p>

<a name="debian"></a>
<span class="heading2">Debian Linux</span><br>

<p>If you have configured <i>apt</i> as illustrated in 
<a href="http://www.debian.org/doc/manuals/apt-howto/index.en.html">the 
APT-HOWTO</a>, run <i>apt-get install postgresql</i> and <i>apt-get install 
postgresql-dev</i>.  If your distribution came with PostgreSQL, you may
be promted to enter a CD.  If not, it will be downloaded from the internet.
When the commands complete, PostgreSQL will be installed.</p>

<a name="slackware"></a>
<span class="heading2">Slackware Linux</span><br>

<p>If there are PostgreSQL packages for Slackware, I've never been able to find 
them.  I've always had to compile from source.</p>

<a name="freebsd"></a>
<span class="heading2">FreeBSD</span><br>

<p>If you have an internet connection, run <i>pkg_add -r postgresql</i>.
When the command completes, PostgreSQL will be installed.  You can also install
PostgreSQL from the Ports CD(s) that came with your distribution using 
<i>/stand/sysinstall</i>.</p>

<a name="openbsd"></a>
<span class="heading2">OpenBSD</span><br>

<p>The postgresql package is available from 
<a href="ftp://ftp.openbsd.org">ftp.openbsd.org</a> or on CD's that came with
your distribution. You can install it using <i>pkg_add</i>.  Once the package 
is installed, you should run the following commands:</p>

<blockquote>
<b>useradd -m postgres</b><br>
<b>passwd postgres</b> (assign the new user a password)<br>
<b>mkdir /usr/local/pgsql /usr/local/pgsql/var</b><br>
<b>chown -R postgres:users /usr/local/pgsql</b><br>
<b>su postgres</b><br>
<b>initdb -D /usr/local/pgsql/var</b><br>
<b>exit</b>
</blockquote>

<a name="netbsd"></a>
<span class="heading2">NetBSD</span><br>

<p>The postgresql and postgresql-clients packages are available from
<a href="ftp://ftp.netbsd.org">ftp.netbsd.org</a> or on CD's that came with 
your distribution. You can install it using <i>pkg_add</i>.  Once the package 
is installed, you should run the following commands:</p>

<blockquote>
<b>useradd -m postgres</b><br>
<b>passwd postgres</b> (assign the new user a password)<br>
<b>mkdir /usr/local/pgsql /usr/local/pgsql/var</b><br>
<b>chown -R postgres:users /usr/local/pgsql</b><br>
<b>su postgres</b><br>
<b>initdb -D /usr/local/pgsql/var</b><br>
<b>exit</b>
</blockquote>

<a name="openserver"></a>
<span class="heading2">SCO OpenServer</span><br>

<p>For SCO OpenServer, PostgreSQL packages are available from the 
<a href="ftp://ftp2.caldera.com/pub/skunkware/osr5/vols">Skunkware ftp 
server</a>.  SCO OpenServer 
packages are often called VOL's because they come as a set of files named 
VOL.000.000, VOL.000.001, etc.  These VOLS can be installed using the Software 
Manager (custom).</p>

<a name="solaris"></a>
<span class="heading2">Solaris</span><br>

<p>If there are PostgreSQL packages for Solaris, I've never been able to find 
them.  I've always had to compile from source.</p>

<a name="source"></a>
<span class="heading2">Compiling From Source</span><br>

<p>If you want to compile PostgreSQL from source, it should compile cleanly
on all of the platforms mentioned above with the exception of SCO OpenServer.
I have never been able to get it to compile on SCO.  The source code is
available from <a href="http://www.postgresql.org">the PostgreSQL site</a>.
You should create a user named postgres and assign it a password, then build
and install PostgreSQL using that user.  By default, PostgreSQL installs 
itself in /usr/local/pgsql.  Note that you should add /usr/local/pgsql/bin to 
your PATH environment variable and /usr/local/pgsql/lib to your 
LD_LIBRARY_PATH environment variable.</p>

<p>After compiling and installing PostgreSQL, you should log in as the
postgres user and run <i>initdb -D /usr/local/pgsql/var</i> to initialize the 
database.</p>

<a name="startingatboot"></a>
<span class="heading2">Starting the Database at Boot Time</span><br>

<p>The package distributions of PostgreSQL install a script which starts the 
database at boot time and stops it at shutdown time.  If you compiled from
source, you'll need to install a script like the following to start/stop the
database at boot/shutdown time.</p>

<blockquote>
<PRE>
<FONT color=#0000ff>#!/bin/sh</FONT>

<B><FONT color=#a62828>case</FONT></B> &quot;$1&quot; <B><FONT color=#a62828>in</FONT></B>
        start<B><FONT color=#a62828>)</FONT></B>
                su <B><FONT color=#a62828>-l</FONT></B> postgres <B><FONT color=#a62828>-s</FONT></B> /bin/sh <B><FONT color=#a62828>-c</FONT></B> <B><FONT color=#a62828>&quot;</FONT></B><FONT color=#ff00ff>/usr/local/pgsql/bin/pg_ctl  -D /usr/local/pgsql/var -p /usr/local/pgsql/bin/postmaster start  &gt; /dev/null 2&gt;&amp;1</FONT><B><FONT color=#a62828>&quot;</FONT></B> <B><FONT color=#a62828>&lt;</FONT></B> /dev/null
                <B><FONT color=#a62828>;;</FONT></B>
        stop<B><FONT color=#a62828>)</FONT></B>
                <B><FONT color=#a62828>kill</FONT></B> <FONT color=#6959cf>`ps </FONT><FONT color=#6959cf>-efa</FONT><FONT color=#6959cf> </FONT><B><FONT color=#a62828>|</FONT></B><FONT color=#6959cf> grep postmaster </FONT><B><FONT color=#a62828>|</FONT></B><FONT color=#6959cf> grep </FONT><FONT color=#6959cf>-v</FONT><FONT color=#6959cf> grep </FONT><B><FONT color=#a62828>|</FONT></B><FONT color=#6959cf> awk </FONT><B><FONT color=#a62828>'</FONT></B><FONT color=#ff00ff>{print $2}</FONT><B><FONT color=#a62828>'</FONT></B><FONT color=#6959cf>`</FONT>
                <B><FONT color=#a62828>;;</FONT></B>
        *<B><FONT color=#a62828>)</FONT></B>
                <B><FONT color=#a62828>echo</FONT></B><FONT color=#ff00ff> $</FONT><B><FONT color=#a62828>&quot;</FONT></B><FONT color=#ff00ff>Usage: </FONT><FONT color=#a620f7>$0</FONT><FONT color=#ff00ff> {start|stop}</FONT><B><FONT color=#a62828>&quot;</FONT></B>
                <B><FONT color=#a62828>exit</FONT></B> <FONT color=#ff00ff>1</FONT>
<B><FONT color=#a62828>esac</FONT></B>

<B><FONT color=#a62828>exit</FONT></B> <FONT color=#ff00ff>0</FONT>
</PRE>
</blockquote>

<p>Install this script and run it with the "start" option to start up the
database.  Running it with the "stop" option shuts the database down.  To
access a database, it must be running.</p>

<a name="initial"></a>
<span class="heading2">Initial Configuration</span><br>

<p>To configure the database, it must be running, so start it up now.</p>

<p>It's necessary to perform administrative tasks as the <i>postgres</i> 
user.  Assigning it a password makes this easier.</p>

<p>Once you have assigned the <i>postgres</i> user a password, log in as
<i>postgres</i>.</p>

<p>Many distributions of PostgreSQL are fairly insecure by default;  any
user on the local machine can connect to the database without supplying a
password.  Some distributions of PostgreSQL are secured using socket
credentials by default.</p>

<p>View the file <i>pg_hba.conf</i> (usually found in <i>/var/lib/pgsql</i>,
<i>/var/lib/pgsql/data</i> or <i>/usr/local/pgsql/var/data</i>) and look for a
line like one of the following:</p>

<blockquote><b><pre>
local        all	ident sameuser

local        all	all			ident sameuser
local        all	all	127.0.0.1/32	ident sameuser
local        all	all	::1/128		ident sameuser
</pre></b></blockquote>

<p>If there's a line like one of those in your file, then all database users
will have the same password as the system password for the user of the same
name, thus, when prompted for a password, enter the password of the user you 
are logged in as (which should be the postgres user).</p>

<p>By default, PostgreSQL has a database called <i>template1</i> containing
privileges and other housekeeping data and an adminstrative user named 
<i>postgres</i>.  Unless your system uses socket credentials, the
<i>postgres</i> database user initially has no password.  To assign it a
password (or to override the password assigned by socket credentials), run a
command like the following.  This command assigns the <i>postgres</i> user the
password <i>newpassword</i>.</p>

<blockquote>
For PostgreSQL 7 or higher:

<blockquote>
<b>psql -c &quot;</b><B><FONT color=#a62828>ALTER</FONT></B> <FONT color=#6959cf>USER</FONT> postgres <FONT color=#6959cf>WITH</FONT> PASSWORD <FONT color=#ff00ff>'newpassword'</FONT>&quot;<b> -d template1</b>
</blockquote>

For PostgreSQL 6:

<blockquote>
<b>psql -c &quot;</b><B><FONT color=#a62828>ALTER</FONT></B> <FONT color=#6959cf>USER</FONT> postgres <FONT color=#6959cf>WITH</FONT> PASSWORD newpassword&quot;<b> -d template1</b>
</blockquote>

</blockquote>

<p>If your system uses socket credentials and you don't want it to, or if it
does not use socket credentials, edit the <i>pg_hba.conf</i> file (usually
found in <i>/var/lib/pgsql</i>, <i>/var/lib/pgsql/data</i> or 
<i>/usr/local/pgsql/var/data</i>) and look for lines like the following:</p>

<blockquote><b><pre>
local	all				ident sameuser
host	all	all	127.0.0.1/32	ident sameuser
host	all	all	::1/128		ident sameuser
</pre></b></blockquote>

<p>and modify these lines to read:</p>

<blockquote><b><pre>
local	all				password
host	all	all	127.0.0.1/32	password
host	all	all	::1/128		password
</pre></b></blockquote>

<p>Older versions of postgresql may have lines like:</p>

<blockquote><b><pre>
local        all                                           trust
host         all         127.0.0.1     255.255.255.255     trust
</pre></b></blockquote>

<p>or like:</p>

<blockquote><b><pre>
local        all                                           ident sameuser
host         all         127.0.0.1     255.255.255.255     ident sameuser
</pre></b></blockquote>

<p>Modify these lines to read:</p>

<blockquote><b><pre>
local        all                                           password
host         all         127.0.0.1     255.255.255.255     password
</pre></b></blockquote>

<p>By default, PostgreSQL listens on a unix socket, but not on an inet socket.
Thus, clients running on the local machine can connect to the database, but
clients running on remote hosts cannot.  You must make some configuration
changes to enable connections from remote hosts.</p>

<p>Look for a file called postgresql.conf in the same directory as pg_hba.conf.
Edit the file and look for a line like one of the following lines:</b>

<blockquote><b><pre>
#listen_addresses = 'localhost' # what IP interface(s) to listen on;
                                # defaults to localhost, '*' = any
</pre></b></blockquote>

<p>If your postgresql.conf file has such a line, add the line:</p>

<blockquote><b><pre>
listen_addresses = '*'
</pre></b></blockquote>

<p>below it.  Older versions of postgresql don't have that parameter.  Instead,
they use the tcpip_socket parameter.  Look for a like like one of:</p>

<blockquote><b><pre>
tcpip_socket = false

#tcpip_socket = false
</pre></b></blockquote>

<p>Change the line to read:</b>

<blockquote><b><pre>
tcpip_socket = true
</pre></b></blockquote>

<p>If the file has no listen_addresses or tcpip_socket line,
then add the tcpip_socket line.</p>

<p>If your distribution has no postgresql.conf file, you will have to modify
the pg_ctl script and add the -i option to the postmaster startup command.
This script has changed a bit from version to version, so it may take a bit of
trial and error to find the exact spot in the file to make the modification.</p>

<p>Once you have configured PostgreSQL listen on an inet socket, you must add a
line to the <i>pg_hba.conf</i> file to allow remote hosts to access local
databases.  The following line allows all hosts in the 192.168.2.0 class C
network to access all databases on the local machine, using password 
authentication.</p>

<blockquote><b><pre>
host	all	all	192.168.2.0/24	password
</pre></b></blockquote>

<p>Older versions of Postgresql need the following line:</p>

<blockquote><b><pre>
host	all	192.168.2.0	255.255.255.0	password
</pre></b></blockquote>

<p>After making these changes, log in as root and restart the database.</p>

<a name="creating"></a>
<span class="heading1">Creating a Database</span><br>

<p>After installation and database initialization, PostgreSQL is ready to 
use but to do any useful work, you'll have to create a database.</p>

<p>In PostgreSQL terminology, a database is a collection of files managed by
a program called <i>postmaster</i>.  Technically, any user can create a 
database and run a <i>postmaster</i> to manage it, but for simplicity's sake, 
we'll just make our database owned by the postgres user.</p>

<p>To create a database named <i>testdb</i> with an administrator named 
<i>postgres</i>, log in as <i>postgres</i> and run the following command.</p>

<blockquote>

For PostgreSQL 7 or higher:

<blockquote>
<b>psql -U postgres -c &quot;</b><B><FONT color=#a62828>CREATE</FONT></B> DATABASE testdb<b>&quot; -d template1</b><br>
<b>Password: newpassword</b>
</blockquote>

For PostgreSQL 6:

<blockquote>
<b>psql -u -c &quot;</b><B><FONT color=#a62828>CREATE</FONT></B> DATABASE testdb<b>&quot; -d template1</b><br>
<b>Username: postgres</b>
<b>Password: newpassword</b>
</blockquote>

</blockquote>


<p>Once the database has been created, you can log into it as follows.</p>

<blockquote>

For PostgreSQL 7 or higher:

<blockquote>
<b>psql -U postgres -d testdb</b><br>
<b>Password: newpassword</b>
</blockquote>


For PostgreSQL 6:

<blockquote>
<b>psql -u -d testdb</b><br>
<b>Username: postgres</b>
<b>Password: newpassword</b>
</blockquote>
</blockquote>

<p>Once logged in, you can create, drop and modify tables and run queries in 
the database.</p>

<p>PostgreSQL authentication is a complex topic.  Lines in the 
<i>pg_hba.conf</i> file define access rules and restrictions for hosts and 
databases.  For more information, consult the PostgreSQL
<a href="http://www.postgresql.org/idocs/">online documentation</a>.</p>

<p>To create database users, use commands like the following.  This command 
creates a user named <i>testuser</i> with password <i>testpassword</i>.</p>

<blockquote>

For PostgreSQL 7 or higher:

<blockquote>
<PRE>
<b>psql -U postgres -c</b> &quot;<B><FONT color=#a62828>CREATE</FONT></B> <FONT color=#6959cf>USER</FONT> testuser <FONT color=#6959cf>WITH</FONT> PASSWORD <FONT color=#ff00ff>'testpassword'</FONT>&quot; <b>-d testdb</b>
<b>Password: newpassword</b>
</PRE>
</blockquote>

For PostgreSQL 6:

<blockquote>
<PRE>
<b>psql -u -c</b> &quot;<B><FONT color=#a62828>CREATE</FONT></B> <FONT color=#6959cf>USER</FONT> testuser <FONT color=#6959cf>WITH</FONT> PASSWORD testpassword&quot; <b>-d testdb</b>
<b>Username: postgres</b>
<b>Password: newpassword</b>
</PRE>
</blockquote>
</blockquote>

<p>You can now log into the database as <i>testuser</i> using the following 
command.</p>

<blockquote>

For PostgreSQL 7 or higher:

<blockquote>
<b>psql -U testuser -d testdb</b><br>
<b>Password: testpassword</b>
</blockquote>

For PostgreSQL 6:

<blockquote>
<b>psql -u -d testdb</b><br>
<b>Username: testuser</b>
<b>Password: testpassword</b>
</blockquote>
</blockquote>

<p>If you want to drop a user,  you can do so.  The following command drops a 
user named <i>testuser</i>.</p>

<blockquote>

For PostgreSQL 7 or higher:

<blockquote>
<PRE>
<B>psql -U postgres -c &quot;<FONT color=#a62828>DROP</FONT></B> <FONT color=#6959cf>USER</FONT> testuser&quot; <b>-d testdb</b>
<b>Password: newpassword</b>
</PRE>
</blockquote>

For PostgreSQL 6:

<blockquote>
<PRE>
<B>psql -u -c &quot;<FONT color=#a62828>DROP</FONT></B> <FONT color=#6959cf>USER</FONT> testuser&quot; <b>-d testdb</b>
<b>Username: postgres</b>
<b>Password: newpassword</b>
</PRE>
</blockquote>

</blockquote>

<p>You may want to add stored procedure support to your database.  PostgreSQL
databases can support stored procedures written in PL/pgSQL, PL/TCL, PL/Perl,
and PL/Python.  Different versions of PostgreSQL support different procedural
languages.</p>

<p>You can use the <i>createlang</i> command to enable support for a stored
procedure language in a particular database.  For example, the following
commands enable support for all languages in the testdb database.</p>

<blockquote><b>
createlang -U postgres -W plpgsql testdb<br>
Password: newpassword </b>(you may be prompted for the password up to 4 times)<b><br>
createlang -U postgres -W pltcl testdb<br>
Password: newpassword </b>(you may be prompted for the password up to 4 times)<b><br>
createlang -U postgres -W plperl testdb<br>
Password: newpassword </b>(you may be prompted for the password up to 4 times)<b><br>
createlang -U postgres -W plpython testdb<br>
Password: newpassword </b>(you may be prompted for the password up to 4 times)<br>
</blockquote>

<p>If you want to drop the database, you can do so with the following 
command.</p>

<blockquote>

For PostgreSQL 7 or higher:

<blockquote>
<PRE>
<B>psql -U postgres -c &quot;<FONT color=#a62828>DROP</FONT></B> DATABASE testdb&quot; <b>-d template1</b>
<b>Password: newpassword</b>
</PRE>
</blockquote>

For PostgreSQL 6:

<blockquote>
<PRE>
<B>psql -u postgres -c &quot;<FONT color=#a62828>DROP</FONT></B> DATABASE testdb&quot; <b>-d template1</b>
<b>Username: postgres</b>
<b>Password: newpassword</b>
</PRE>
</blockquote>

</blockquote>

<p>This should be enough to get you started.  To set up more complex 
configurations, consult the 
<a href="http://www.postgresql.org/idocs/">online documentation</a>.</p>

<a name="accessing"></a>
<span class="heading1">Accessing a Database</span><br>

<p>Accessing a PostgreSQL database using the <i>psql</i> client tool is simple.
For example, to access a database called <i>testdb</i> on the local machine as
the <i>testuser</i> user with password <i>testpassword</i>, use the following
command.</p>

<blockquote>

For PostgreSQL 7 or higher:

<blockquote>
<b>psql -U testuser -d testdb</b><br>
<b>Password: testpassword</b><br>
</blockquote>

For PostgreSQL 6:

<blockquote>
<b>psql -u -d testdb</b><br>
<b>Username: testuser</b><br>
<b>Password: testpassword</b><br>
</blockquote>
</blockquote>

<p>If you want to access a database on a remote machine, say on 
<i>testhost</i>, use the -h option as follows.</p>

<blockquote>

For PostgreSQL 7 or higher:

<blockquote>
<b>psql -U testuser -h testhost -d testdb</b><br>
<b>Password: testpassword</b><br>
</blockquote>

For PostgreSQL 6:

<blockquote>
<b>psql -u -h testhost -d testdb</b><br>
<b>Password: testuser</b><br>
<b>Password: testpassword</b><br>
</blockquote>
</blockquote>

<p>Once you're connected to the database, the <i>psql</i> client prompts you to
enter a query.  Queries may be split across multiple lines.  To run a query, 
end it with a semicolon or type \g on the next line.  To exit, type \q.</p>

<p>A sample <i>psql</i> session follows.</p>

<PRE>
[user@localhost user]$ psql -U testuser testdb
Password: 
Welcome to psql, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

testdb=&gt; <B><FONT color=#a62828>create</FONT></B> <FONT color=#6959cf>table</FONT> testtable (
testdb(&gt; col1 <B><FONT color=#288a51>char</FONT></B>(<FONT color=#ff00ff>40</FONT>),
testdb(&gt; col2 <B><FONT color=#288a51>integer</FONT></B>
testdb(&gt; );
<B><FONT color=#a62828>CREATE</FONT></B>
testdb=&gt; <B><FONT color=#a62828>select</FONT></B> * <FONT color=#6959cf>from</FONT> pg_tables <FONT color=#6959cf>where</FONT> tableowner=<FONT color=#ff00ff>'testuser'</FONT>;
 tablename | tableowner | hasindexes | hasrules | hastriggers 
<FONT color=#0000ff>-----------+------------+------------+----------+-------------</FONT>
 testtable | testuser   | f          | f        | f
(<FONT color=#ff00ff>1</FONT> row)

testdb=&gt; \d testtable
          Table <FONT color=#ff00ff>&quot;testtable&quot;</FONT>
 Attribute |     Type      | Modifier 
<FONT color=#0000ff>-----------+---------------+----------</FONT>
 col1      | <B><FONT color=#288a51>character</FONT></B>(<FONT color=#ff00ff>40</FONT>) | 
 col2      | <B><FONT color=#288a51>integer</FONT></B>       | 

testdb=&gt; <B><FONT color=#a62828>insert</FONT></B> <FONT color=#6959cf>into</FONT> testtable <FONT color=#6959cf>values</FONT> (<FONT color=#ff00ff>'hello'</FONT>,<FONT color=#ff00ff>50</FONT>);
<B><FONT color=#a62828>INSERT</FONT></B> <FONT color=#ff00ff>468520</FONT> <FONT color=#ff00ff>1</FONT>
testdb=&gt; <B><FONT color=#a62828>insert</FONT></B> <FONT color=#6959cf>into</FONT> testtable <FONT color=#6959cf>values</FONT> (<FONT color=#ff00ff>'hi'</FONT>,<FONT color=#ff00ff>60</FONT>);   
<B><FONT color=#a62828>INSERT</FONT></B> <FONT color=#ff00ff>468521</FONT> <FONT color=#ff00ff>1</FONT>
testdb=&gt; <B><FONT color=#a62828>insert</FONT></B> <FONT color=#6959cf>into</FONT> testtable <FONT color=#6959cf>values</FONT> (<FONT color=#ff00ff>'bye'</FONT>,<FONT color=#ff00ff>70</FONT>);
<B><FONT color=#a62828>INSERT</FONT></B> <FONT color=#ff00ff>468522</FONT> <FONT color=#ff00ff>1</FONT>
testdb=&gt; <B><FONT color=#a62828>select</FONT></B> * <FONT color=#6959cf>from</FONT> testtable;
                   col1                   | col2 
<FONT color=#0000ff>------------------------------------------+------</FONT>
 hello                                    |   <FONT color=#ff00ff>50</FONT>
 hi                                       |   <FONT color=#ff00ff>60</FONT>
 bye                                      |   <FONT color=#ff00ff>70</FONT>
(<FONT color=#ff00ff>3</FONT> rows)

testdb=&gt; <B><FONT color=#a62828>update</FONT></B> testtable <B><FONT color=#a62828>set</FONT></B> col2=<FONT color=#ff00ff>0</FONT> <FONT color=#6959cf>where</FONT> col1=<FONT color=#ff00ff>'hi'</FONT>;
<B><FONT color=#a62828>UPDATE</FONT></B> <FONT color=#ff00ff>1</FONT>
testdb=&gt; <B><FONT color=#a62828>select</FONT></B> * <FONT color=#6959cf>from</FONT> testtable;
                   col1                   | col2 
<FONT color=#0000ff>------------------------------------------+------</FONT>
 hello                                    |   <FONT color=#ff00ff>50</FONT>
 bye                                      |   <FONT color=#ff00ff>70</FONT>
 hi                                       |    <FONT color=#ff00ff>0</FONT>
(<FONT color=#ff00ff>3</FONT> rows)

testdb=&gt; <B><FONT color=#a62828>delete</FONT></B> <FONT color=#6959cf>from</FONT> testtable <FONT color=#6959cf>where</FONT> col2=<FONT color=#ff00ff>50</FONT>;
<B><FONT color=#a62828>DELETE</FONT></B> <FONT color=#ff00ff>1</FONT>
testdb=&gt; <B><FONT color=#a62828>select</FONT></B> * <FONT color=#6959cf>from</FONT> testtable;
                   col1                   | col2 
<FONT color=#0000ff>------------------------------------------+------</FONT>
 bye                                      |   <FONT color=#ff00ff>70</FONT>
 hi                                       |    <FONT color=#ff00ff>0</FONT>
(<FONT color=#ff00ff>2</FONT> rows)

testdb=&gt; <B><FONT color=#a62828>drop</FONT></B> <FONT color=#6959cf>table</FONT> testtable;
<B><FONT color=#a62828>DROP</FONT></B>
testdb=&gt; \q
</PRE>

<a name="sqlrelay"></a>
<span class="heading1">Accessing a Database With SQL Relay</span><br>

<p>Accessing PostgreSQL from SQL Relay requires an instance entry in your 
<i>sqlrelay.conf</i> file for the database that you want 
to access.  Here is an example <i>sqlrelay.conf</i> which defines an SQL Relay 
instance called postgresqltest.  This instance connects to the <i>testdb</i> 
database running on the remote server <i>testhost</i> as the user 
<i>testuser</i> with password <i>testpassword</i>.</p>

<PRE>
<FONT color=#0000ff>&lt;?</FONT><B><FONT color=#288a51>xml version=</FONT></B><FONT color=#ff00ff>&quot;1.0&quot;</FONT><FONT color=#0000ff>?&gt;</FONT>
<FONT color=#008a8e>&lt;!</FONT><B><FONT color=#a62828>DOCTYPE</FONT></B> instances <B><FONT color=#a62828>SYSTEM</FONT></B> <FONT color=#ff00ff>&quot;sqlrelay.dtd&quot;</FONT><FONT color=#008a8e>&gt;</FONT>
<FONT color=#008a8e>&lt;instances&gt;</FONT>

        <FONT color=#008a8e>&lt;instance id=</FONT><FONT color=#ff00ff>&quot;postgresqltest&quot;</FONT><FONT color=#008a8e> port=</FONT><FONT color=#ff00ff>&quot;9000&quot;</FONT><FONT color=#008a8e> socket=</FONT><FONT color=#ff00ff>&quot;/tmp/postgresqltest.socket&quot;</FONT><FONT color=#008a8e> dbase=</FONT><FONT color=#ff00ff>&quot;postgresql&quot;</FONT><FONT color=#008a8e> connections=</FONT><FONT color=#ff00ff>&quot;3&quot;</FONT><FONT color=#008a8e> maxconnections=</FONT><FONT color=#ff00ff>&quot;5&quot;</FONT><FONT color=#008a8e> maxqueuelength=</FONT><FONT color=#ff00ff>&quot;0&quot;</FONT><FONT color=#008a8e> growby=</FONT><FONT color=#ff00ff>&quot;1&quot;</FONT><FONT color=#008a8e> ttl=</FONT><FONT color=#ff00ff>&quot;60&quot;</FONT><FONT color=#008a8e> endofsession=</FONT><FONT color=#ff00ff>&quot;commit&quot;</FONT><FONT color=#008a8e> sessiontimeout=</FONT><FONT color=#ff00ff>&quot;600&quot;</FONT><FONT color=#008a8e> runasuser=</FONT><FONT color=#ff00ff>&quot;nobody&quot;</FONT><FONT color=#008a8e> runasgroup=</FONT><FONT color=#ff00ff>&quot;nobody&quot;</FONT><FONT color=#008a8e> cursors=</FONT><FONT color=#ff00ff>&quot;5&quot;</FONT><FONT color=#008a8e> authtier=</FONT><FONT color=#ff00ff>&quot;listener&quot;</FONT><FONT color=#008a8e> handoff=</FONT><FONT color=#ff00ff>&quot;pass&quot;</FONT><FONT color=#008a8e>&gt;</FONT>
                <FONT color=#008a8e>&lt;users&gt;</FONT>
                        <FONT color=#008a8e>&lt;user user=</FONT><FONT color=#ff00ff>&quot;postgresqltest&quot;</FONT><FONT color=#008a8e> password=</FONT><FONT color=#ff00ff>&quot;postgresqltest&quot;</FONT><FONT color=#008a8e>/&gt;</FONT>
                <FONT color=#008a8e>&lt;/users&gt;</FONT>
                <FONT color=#008a8e>&lt;connections&gt;</FONT>
                        <FONT color=#008a8e>&lt;connection connectionid=</FONT><FONT color=#ff00ff>&quot;postgresqltest&quot;</FONT><FONT color=#008a8e> string=</FONT><FONT color=#ff00ff>&quot;host=testhost;db=testdb;user=testuser;password=testpassword&quot;</FONT><FONT color=#008a8e> metric=</FONT><FONT color=#ff00ff>&quot;1&quot;</FONT><FONT color=#008a8e>/&gt;</FONT>
                <FONT color=#008a8e>&lt;/connections&gt;</FONT>
        <FONT color=#008a8e>&lt;/instance&gt;</FONT>

<FONT color=#008a8e>&lt;/instances&gt;</FONT>
</PRE>

<p>Now you can start up this instance with the following command.</p>

<blockquote>
<b>sqlr-start -id postgresqltest</b>
</blockquote>

<p>To connect to the instance and run queries, use the following command.</p>

<blockquote>
<b>sqlrsh -id postgresqltest</b>
</blockquote>

<p>The following command shuts down the SQL Relay instance.</p>

<blockquote>
<b>sqlr-stop postgresqltest</b>
</blockquote>

<a name="quirks"></a>
<span class="heading1">PostgreSQL Quirks</span><br>

<p>PostgreSQL transactions must be explicitly started.  When you log into
a PostgreSQL database, the session is in autocommit mode.  If you run an
insert, update or delete query, it will be committed immediately.  The session
is in autocommit mode until a "begin" query is run to begin a transaction.
After beginning a transaction, inserts, updates and deletes are performed
within the context of the transaction.  A commit or rollback must be run to
end the transaction.  After a commit or rollback is run, the session goes back
into autocommit mode.</p>

</body>

</html>
